package auto.utils;

import com.kipa.utils.PrintUtils;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;

/**
 * 从Excel中读取数据
 * @author jue.wang
 * @date 2021/5/17-9:34
 */
public class ExcelUtils {

    public static String[][] getExcel(String filePath){
        File file = new File(filePath);
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
        } catch (IOException e) {
            PrintUtils.println(e);
        }
        XSSFSheet sheet=workbook.getSheetAt(0);
        //获取数据行数（行号从0开始，序号也从0开始）
        int rowNum=sheet.getLastRowNum() + 1;
        //获取数据列数（列号从1开始，但序号从0开始）
        int columnNum = sheet.getRow(0).getLastCellNum();
        //将Excel中的数据读取到数组中
        String[][] arr = new String[rowNum][columnNum];
        for (int i=0; i<rowNum; i++){
            XSSFRow row = sheet.getRow(i);
            for (int j=0;j<columnNum;j++){
                XSSFCell cell = row.getCell(j);
                cell.setCellType(CellType.STRING);
                arr[i][j] = cell.toString().trim();
            }
        }
        return arr;
    }

    public static Object[][] toProvider(String filePath, int startNum, int endNum){
        //源数据
        String[][] arr = getExcel(filePath);
        //获取数组行列数
        int rowNum = arr.length;
        int columnNum = arr[0].length;
        //将源数据转换成目标数据
        Object[][] arr1 = new Object[rowNum-1][columnNum-endNum+startNum];
        for(int x=1; x<rowNum; x++){
                for(int y1=0; y1<startNum-1; y1++){
                    arr1[x-1][y1] = arr[x][y1];
                    PrintUtils.println(arr1[x-1][y1]);
                }
                String request = "";
                for(int y2 = startNum-1; y2<endNum; y2++){
                        if ("[none]".equals(arr[x][y2])){}
                        else if("null".equals(arr[x][y2])){
                            String request1 = "\"" + arr[0][y2] + "\"" +":null";
                            request = String.join(",",request,request1);
                        }
                        else{
                           // String request1 = "\"" + arr[0][y2] + "\"" + ":" + arr[x][y2];
                            String request1 =  arr[x][y2];
                            request = String.join(",",request,request1);
                        }
                    }
                if(!"".equals(request)){
                    request = request.substring(1);
                }
                request = String.join("","{",request,"}");
                arr1[x-1][startNum-1] = request;
                PrintUtils.println(arr1[x-1][startNum-1]);
                for(int y3=endNum; y3<columnNum; y3++){
                    arr1[x-1][y3-endNum+startNum] = arr[x][y3];
                    PrintUtils.println(arr1[x-1][y3-endNum+startNum]);
                }
            }
        return arr1;
    }

}
